//08290016
#include"dbClass.hpp"
using namespace std;
/*构造函数，传入参数是否输出调试信息*/
dbClass::dbClass(bool is_output_ip = 0){
    is_output = is_output_ip;
    sql_conn = SqlConn(sql_conn);
}
/*析构*/
dbClass::~dbClass(){
    CloseSqlConn(sql_conn);
}
/*获取sql数据库连接*/
MYSQL* dbClass::SqlConn(MYSQL* sql_conn){
    sql_conn = mysql_init(NULL);
    mysql_thread_init();
    if(mysql_real_connect(sql_conn,"localhost","root","Mysql-123456","ChatServerDB",0,NULL,0) == NULL){
        if(is_output)    cout << "err:db connect fial\n";
    }
    return sql_conn;
}
/*关闭sql数据库连接*/
uint8_t dbClass::CloseSqlConn(MYSQL* sql_conn){
    if(sql_conn){
		mysql_close(sql_conn);
        mysql_thread_end();
	}
    else{
        if(is_output)    cout << "db closed err!\n";
    }
}
/*删除id前缀0，可处理"03,0400,"形状的id串*/
string & dbClass::ClearZero(string &str){
    while(str[0]=='0'){
        str.erase(0,1); //若str首字节为0，则删除首字节循环直到首字节不为0
    }
    int i = 0;
    while(i < str.length()){
        if(str[i] == ','){
            i += 1; //若 , ，看下一字节是否为0
            while(str[i]=='0'){
                str.erase(i,1); //若,后首字节为0，则删除首字节循环直到首字节不为0
            }
        }
        i++;
    }
    return str;
}
/*输入id判断是用户还是群聊，群聊id起始为800000，用户id起始为100000*/
bool dbClass::is_chatGroup(string &id){
    if(id[0] >= '8'){
        return true;
    }
    return false;
}

/*注册用户并确定job，返回用户id*/
string dbClass::AddUser(string uName, string pwd, string job){
    string rt_uID = "";
    // MYSQL* sql_conn = SqlConn(sql_conn);
    try{
        string ip = "0";
        string insert_sql =
            "insert into userAccountTable(userName,userPassword,userIP,userJob) values(""'"+uName+"'"",MD5(""'"+pwd+"'""),""'"+ip+"'"",""'"+job+"'"")";
        if(mysql_query(sql_conn, insert_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            throw "-1";
        }
        string last_id_sql ="SELECT LAST_INSERT_ID()";
        if(mysql_query(sql_conn, last_id_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<last_id_sql<<"\n";  
            throw "-1";
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
            if(is_output)    cout << "err:LAST_INSERT_ID!\n";
            throw "-1";
        }
        MYSQL_ROW row_uID = mysql_fetch_row(select_result);
        rt_uID = row_uID[0];
        // CloseSqlConn(sql_conn);
    }catch(const char* &e){
        cout<<"AddUser :"<<e<<endl;
    }
    return rt_uID;
}
/*用户登录，正常返回0，id不存在返回2，密码不对返回3*/
uint8_t dbClass::CheckSignin(string uID,string pwd){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    string check_is_exist_sql = "select userPassword from userAccountTable where userID = ""'" + uID + "'""";
    if(mysql_query(sql_conn, check_is_exist_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<check_is_exist_sql<<"\n";
        return 1;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
        if(is_output)    cout << "err:user not existed!\n";
        // CloseSqlConn(sql_conn);
        return 2;
    }
    MYSQL_ROW row_db_password = mysql_fetch_row(select_result);
    string db_password = row_db_password[0];   //获取到数据库存储的加密后的密码

    string MD5_ipPwd_sql = "select MD5(""'" + pwd + "'"")";
    if(mysql_query(sql_conn, MD5_ipPwd_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<MD5_ipPwd_sql<<"\n";
        return 1;
    }
    MYSQL_RES *select_result_MD5_inPwd_sql = mysql_store_result(sql_conn);    //获取上面查询的结果
    if(mysql_num_rows(select_result_MD5_inPwd_sql)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
        if(is_output)    cout << "err:md5!\n";
        return 2;
    }
    MYSQL_ROW row_in_password = mysql_fetch_row(select_result_MD5_inPwd_sql);
    string in_password = row_in_password[0];   //获取到输入密码的MD5

    if(strcmp(in_password.c_str(),db_password.c_str()) == 0){//输入密码的MD5==数据库存储的加密后的密码，密码正确
        if(is_output)    cout << "Signin\n";
    }
    else{
        if(is_output)    cout << "err:pwd not right!\n";
        // CloseSqlConn(sql_conn);
        return 3;
    }
    // CloseSqlConn(sql_conn);
    return 0;
}
/*记录用户ip，下线了记为"0"，正常返回0*/
uint8_t dbClass::AlterUserIP(string uID,string ip){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    string alter_ip_sql = "update userAccountTable set userIP = ""'" + ip + "'"" where userID = ""'" + uID + "'""";
    if(mysql_query(sql_conn, alter_ip_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<alter_ip_sql<<"\n";
        return 1;
    }
    // CloseSqlConn(sql_conn);
    return 0;
}
/*获取用户ip，下线了记为"0"*/
string dbClass::GetUserIP(string uID){
    string rt_ip = "";
    // MYSQL* sql_conn = SqlConn(sql_conn);
    try{
        string get_ip_sql = "select userIP from userAccountTable where userID = ""'" + uID + "'""";
        if(mysql_query(sql_conn, get_ip_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回-1
            if(is_output)    cout << "err:sql!"<<get_ip_sql<<"\n";
            throw "-1";
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回-2
            if(is_output)    cout << "err:user not existed!\n";
            throw "-2";
        }
        MYSQL_ROW row_ip = mysql_fetch_row(select_result);
        rt_ip = row_ip[0];
        // CloseSqlConn(sql_conn);
    }catch(const char* &e){
        cout<<"GetUserIP :"<<e<<endl;
        string rt_ip = "-1";
        // CloseSqlConn(sql_conn);
    }
    return rt_ip;
}
/*获取全部好友名、ip、job，其中ip包含了在线信息，格式为：[[id,name,ip,job],...]*/
vector< vector<string> > dbClass::GetUserList(){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > userlist;
    string check_is_exist_sql = "select userID,userName,userIP,userJob from userAccountTable";
    if(mysql_query(sql_conn, check_is_exist_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<check_is_exist_sql<<"\n";
        return userlist;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    //int row_num = mysql_num_rows(select_result);
    while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
        vector<string> tmp_vec;
        for(int j_column=0; j_column < 4; j_column ++ ){
            string tmp_str = row_user[j_column];
            tmp_vec.push_back(tmp_str);
        }
        userlist.push_back(tmp_vec);
    }
    // CloseSqlConn(sql_conn);
    return userlist;
}
/*添加群聊，userList格式为："id1,id2,...,idn,"*/
string dbClass::AddChatGroup(string groupName,string userList, string groupCategory){
    string rt_gID = "";
    // MYSQL* sql_conn = SqlConn(sql_conn);
    try{
        userList = ClearZero(userList);
        string insert_sql =
            "insert into chatGroupTable(groupName ,memberList ,groupCategory) values(""'"+groupName+"'"",""'"+userList+"'"",""'"+groupCategory+"'"")";
        if(mysql_query(sql_conn, insert_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<insert_sql<<"\n";  
            throw "-1";
        }
        string last_id_sql ="SELECT LAST_INSERT_ID()";
        if(mysql_query(sql_conn, last_id_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<last_id_sql<<"\n";  
            throw "-1";
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
            if(is_output)    cout << "err:LAST_INSERT_ID!\n";
            throw "-1";
        }
        MYSQL_ROW row_gID = mysql_fetch_row(select_result);
        rt_gID = row_gID[0];
        // CloseSqlConn(sql_conn);
    }catch(const char* &e){
        cout<<"AddChatGroup :"<<e<<endl;
        // CloseSqlConn(sql_conn);
    }
    return rt_gID;
}
/*获取含有自己的全部群组，格式为：[[id,name,category,memberList],...]*/
vector< vector<string> > dbClass::GetChatGroupList(string myUserID){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    myUserID = ClearZero(myUserID);
    vector< vector<string> > grouplist;
    string check_is_exist_sql = "select groupID,groupName,groupCategory,memberList from chatGroupTable where FIND_IN_SET(""'"+myUserID+"'"",memberList)";
    if(mysql_query(sql_conn, check_is_exist_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<check_is_exist_sql<<"\n";
        return grouplist;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    //int row_num = mysql_num_rows(select_result);
    while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
        vector<string> tmp_vec;
        for(int j_column=0; j_column < 4; j_column ++ ){
            string tmp_str = row_user[j_column];
            tmp_vec.push_back(tmp_str);
        }
        grouplist.push_back(tmp_vec);
    }
    // CloseSqlConn(sql_conn);
    return grouplist;
}
/*获取群组包含的全部user，格式为：[[id,name,ip,job],...]*/
vector< vector<string> > dbClass::GetGroupUserList(string chatGroupID){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > user_name_list;
    string get_userList_sql = "select memberList from chatGroupTable where groupID = ""'" + chatGroupID + "'""";
    if(mysql_query(sql_conn, get_userList_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回-1
        if(is_output)    cout << "err:sql!"<<get_userList_sql<<"\n";
        return user_name_list;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回-2
        if(is_output)    cout << "err:group not existed!\n";
        // CloseSqlConn(sql_conn);
        return user_name_list;
    }
    MYSQL_ROW row_userList = mysql_fetch_row(select_result);
    string str_userList = row_userList[0];  //得到了该群的成员列表

    str_userList = ClearZero(str_userList);
    string get_userNameList_sql = "select userID,userName,userIP,userJob from userAccountTable where FIND_IN_SET(userID,""'"+str_userList+"'"")";
    if(mysql_query(sql_conn, get_userNameList_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<get_userNameList_sql<<"\n";
        return user_name_list;
    }
    MYSQL_RES *select_result2 = mysql_store_result(sql_conn);    //获取上面查询的结果
    while(MYSQL_ROW row_userName = mysql_fetch_row(select_result2)){
        vector<string> tmp_vec;
        for(int j_column=0; j_column < 4; j_column ++ ){
            string tmp_str = row_userName[j_column];
            tmp_vec.push_back(tmp_str);
        }
        user_name_list.push_back(tmp_vec);
    }
    // CloseSqlConn(sql_conn);
    return user_name_list;
}
/*更新用户心跳包时间*/
uint8_t dbClass::SaveUserBeatTime(string userID, string time){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    string alter_beatTime_sql = "update userAccountTable set lastBeatTime  = ""'" + time + "'"" where userID = ""'" + userID + "'""";
    if(mysql_query(sql_conn, alter_beatTime_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<alter_beatTime_sql<<"\n";
        return 1;
    }
    // CloseSqlConn(sql_conn);
    return 0;
}
/*获取用户上次心跳包时间*/
string dbClass::GetUserBeatTime(string userID){
    string rt_beatTime = "";
    // MYSQL* sql_conn = SqlConn(sql_conn);
    try{
        string get_beatTime_sql = "select lastBeatTime from userAccountTable where userID = ""'" + userID + "'""";
        if(mysql_query(sql_conn, get_beatTime_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回-1
            if(is_output)    cout << "err:sql!"<<get_beatTime_sql<<"\n";
            throw "-1";
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回-2
            if(is_output)    cout << "err:user not existed!\n";
            throw "-2";
        }
        MYSQL_ROW row_beatTime = mysql_fetch_row(select_result);
        rt_beatTime = row_beatTime[0];
        // CloseSqlConn(sql_conn);
    }catch(const char* &e){
        cout<<"GetUserBeatTime :"<<e<<endl;
        // CloseSqlConn(sql_conn);
    }
    return rt_beatTime;
}
/*存储一条聊天信息，若为群聊：RUserID为群聊id*/
string dbClass::SaveMessage(string TUserID, string RUserID, string message, string messageType){
    string rt_messageID = "";
    // MYSQL* sql_conn = SqlConn(sql_conn);
    try{
        TUserID = ClearZero(TUserID);
        RUserID = ClearZero(RUserID);
        string time_sql ="select now()";
        if(mysql_query(sql_conn, time_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<time_sql<<"\n";  
            throw "-1";
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
            if(is_output)    cout << "err:SELECT NOW()!\n";
            throw "-1";
        }
        MYSQL_ROW row_time = mysql_fetch_row(select_result);
        string insert_sql =
            "insert into chatLogTable(TUserID,RUserID,message,time,messageType) values(""'"+TUserID+"'"",""'"+RUserID+"'"",""'"+message+"'"",""'"+row_time[0]+"'"",""'"+messageType+"'"")";
        if(mysql_query(sql_conn, insert_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<insert_sql<<"\n";  
            throw "-1";
        }
        string last_messageid_sql ="SELECT LAST_INSERT_ID()";
        if(mysql_query(sql_conn, last_messageid_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<last_messageid_sql<<"\n";  
            throw "-1";
        }
        MYSQL_RES *select_result_messageid = mysql_store_result(sql_conn);    //获取上面查询的结果
        if(mysql_num_rows(select_result_messageid)==0){ //获取上面结果的行数，若为0则不存在该用户id返回2
            if(is_output)    cout << "err:LAST_INSERT_ID!\n";
            throw "-1";
        }
        MYSQL_ROW row_messageID = mysql_fetch_row(select_result_messageid);
        rt_messageID = row_messageID[0];
        // CloseSqlConn(sql_conn);
    }catch(const char* &e){
        cout<<"SaveMessage :"<<e<<endl;
        // CloseSqlConn(sql_conn);
    }
    return rt_messageID;
}
/*获取本用户某聊天或群聊末条未读消息（传入上次离开该群聊的时间，若为群聊id为RUserID）返回格式为：[message,time]*/
/*vector< string > dbClass::GetLastestMessage(string TUserID, string RUserID, string time){
    MYSQL* sql_conn = SqlConn(sql_conn);
    vector< string > latest_message;
    TUserID = ClearZero(TUserID);
    RUserID = ClearZero(RUserID);
    if(is_chatGroup(RUserID)){  //如果是群，到表里通过群号找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
        "select message,time,messageType from chatLogTable where RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < time ORDER BY time ASC LIMIT 1";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return latest_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 3; j_column ++ ){
                string tmp_str = row_user[j_column];
                tmp_vec.push_back(tmp_str);
            }
            latest_message = tmp_vec;
        }
    }
    else{  //如果是用户id，到表里通过T、Rid找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
        "select message,time,messageType from chatLogTable where TUserID = ""'"+TUserID+"'"" and RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < time ORDER BY time ASC LIMIT 1";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return latest_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 3; j_column ++ ){
                string tmp_str = row_user[j_column];
                tmp_vec.push_back(tmp_str);
            }
            latest_message = tmp_vec;
        }
    }
    CloseSqlConn(sql_conn);
    return latest_message;
}*/
/*获取本用户某聊天或群聊未读消息数（传入上次进入该群聊的时间，若为群聊id为RUserID）*/
/*int dbClass::GetMessageNum(string TUserID, string RUserID, string time){
    MYSQL* sql_conn = SqlConn(sql_conn);
    TUserID = ClearZero(TUserID);
    RUserID = ClearZero(RUserID);
    int message_num = 0;
    if(is_chatGroup(RUserID)){  //如果是群，到表里通过群号找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
        "select RUserID from chatLogTable where RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < time ";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return -1;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        message_num = mysql_num_rows(select_result);
    }
    else{  //如果是用户id，到表里通过T、Rid找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
            "select RUserID from chatLogTable where TUserID = ""'"+TUserID+"'"" and RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < time";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return -1;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        message_num = mysql_num_rows(select_result);
    }
    CloseSqlConn(sql_conn);
    return message_num;
}*/
/*获取某聊天或群聊的某时间点前至多10条未读消息，格式为：[[id,name,message,time],...]*/
/*vector< vector<string> > dbClass::GetPieceofMessage(string TUserID, string RUserID, string time){
    MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > pieceof_message;
    TUserID = ClearZero(TUserID);
    RUserID = ClearZero(RUserID);
    if(is_chatGroup(RUserID)){  //如果是群，到表里通过群号找群内最新消息ORDER BY price ASC LIMIT 1;
    // select b.userName,b.userID,a.message,a.time from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.RUserID = '1000000' and '2023-08-24 08:40:57' < a.time ORDER BY time ASC LIMIT 10;
        string search_mess_sql = 
        "select b.userID,b.userName,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < a.time ORDER BY time ASC LIMIT 10";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 5; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    }
    else{  //如果是用户id，到表里通过T、Rid找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
        "select b.userID,b.userName,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.TUserID = ""'"+TUserID+"'"" and a.RUserID = ""'"+RUserID+"'"" and ""'"+time+"'"" < a.time ORDER BY time ASC LIMIT 10";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 5; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    }
    CloseSqlConn(sql_conn);
    return pieceof_message;
}*/
/*获取某聊天或群聊的某时间点前全部未读消息，格式为：[[id,name,messageID,message,time,type],...]*/
/*vector< vector<string> > dbClass::GetAllMessage(string TUserID, string RUserID){
    MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > pieceof_message;
    TUserID = ClearZero(TUserID);
    RUserID = ClearZero(RUserID);
    if(is_chatGroup(RUserID)){  //如果是群，到表里通过群号找群内最新消息ORDER BY price ASC LIMIT 1;
    // select b.userName,b.userID,a.message,a.time from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.RUserID = '1000000' and '2023-08-24 08:40:57' < a.time ORDER BY time ASC LIMIT 10;
        string search_mess_sql = 
        "select b.userID,b.userName,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.RUserID = ""'"+RUserID+"'"" ORDER BY time ASC";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 5; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    }
    else{  //如果是用户id，到表里通过T、Rid找群内最新消息ORDER BY price ASC LIMIT 1;
        string search_mess_sql = 
        "select b.userID,b.userName,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID=b.userID and a.TUserID = ""'"+TUserID+"'"" and a.RUserID = ""'"+RUserID+"'"" ORDER BY time ASC";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 5; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    }
    CloseSqlConn(sql_conn);
    return pieceof_message;
}*/
/*获取某用户的本地数据库最新消息后的消息，传入用户id和其本地最新消息id，没有则返回空，格式为：[[Tid,Tname,Rid,messageID,message,time,type],...]*/
vector< vector<string> > dbClass::GetNewMessage(string myUserID, string lastMessageID){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > pieceof_message;
    myUserID = ClearZero(myUserID);
    //获取发给自己的新消息
        string search_mess_sql = 
        "select b.userID,b.userName,a.RUserID,a.messageID,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID = b.userID and a.RUserID = ""'"+myUserID+"'"" and a.messageID > ""'"+lastMessageID+"'"" ORDER BY a.messageID ASC";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        //int row_num = mysql_num_rows(select_result);
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 7; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    //获取发给自己所在群的新消息
    string check_is_exist_sql = "select groupID from chatGroupTable where FIND_IN_SET(""'"+myUserID+"'"",memberList)";
    if(mysql_query(sql_conn, check_is_exist_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<check_is_exist_sql<<"\n";
        return pieceof_message;
    }
    MYSQL_RES *select_result_check_is_exist_sql = mysql_store_result(sql_conn);    //获取上面查询的结果
    while(MYSQL_ROW row_groupID = mysql_fetch_row(select_result_check_is_exist_sql)){
        string str_groupID = row_groupID[0];
        string search_mess_sql = 
            "select b.userID,b.userName,a.RUserID,a.messageID,a.message,a.time,a.messageType from chatLogTable as a inner join userAccountTable as b on a.TUserID = b.userID and a.RUserID = ""'"+ str_groupID +"'"" and a.messageID > ""'"+lastMessageID+"'"" ORDER BY a.messageID ASC";
        if(mysql_query(sql_conn, search_mess_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
            if(is_output)    cout << "err:sql!"<<search_mess_sql<<"\n";
            return pieceof_message;
        }
        MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
        while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
            vector<string> tmp_vec;
            for(int j_column=0; j_column < 7; j_column ++ ){
                string tmp_str = row_user[j_column];

                tmp_vec.push_back(tmp_str);
            }
            pieceof_message.push_back(tmp_vec);
        }
    }
    // CloseSqlConn(sql_conn);
    return pieceof_message;
}
/*获取某用户的本地数据库最新user后的user，传入用户id和其本地最大userID，没有则返回空vector，格式为：[[userID,name,ip,job],...]*/
vector< vector<string> > dbClass::GetNewUser(string lastUserID){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    vector< vector<string> > new_pieceof_user;
    //获取对于本地数据库的新联系人
    string search_user_sql = 
        "select userID,userName,userIP,userJob from userAccountTable and userID > ""'"+lastUserID+"'"" ORDER BY userID ASC";
    if(mysql_query(sql_conn, search_user_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<search_user_sql<<"\n";
        return new_pieceof_user;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    //int row_num = mysql_num_rows(select_result);
    while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
        vector<string> tmp_vec;
        for(int j_column=0; j_column < 4; j_column ++ ){
            string tmp_str = row_user[j_column];
            tmp_vec.push_back(tmp_str);
        }
        new_pieceof_user.push_back(tmp_vec);
    }
    // CloseSqlConn(sql_conn);
    return new_pieceof_user;
}
/*获取某用户的本地数据库最新group后的group，传入用户id和其本地最大groupID，没有则返回空vector，格式为：[[groupid,name,category,memberList],...]*/
vector< vector<string> > dbClass::dbClass::GetNewGroup(string myUserID, string lastGroupID){
    // MYSQL* sql_conn = SqlConn(sql_conn);
    myUserID = ClearZero(myUserID);
    vector< vector<string> > grouplist;
    string check_is_exist_sql = 
        "select groupID,groupName,groupCategory,memberList from chatGroupTable where FIND_IN_SET(""'"+myUserID+"'"",memberList) and groupID > ""'"+lastGroupID+"'""";
    if(mysql_query(sql_conn, check_is_exist_sql.c_str())){  //每个sql查询检查返回值若为!0则出错返回1
        if(is_output)    cout << "err:sql!"<<check_is_exist_sql<<"\n";
        return grouplist;
    }
    MYSQL_RES *select_result = mysql_store_result(sql_conn);    //获取上面查询的结果
    //int row_num = mysql_num_rows(select_result);
    while(MYSQL_ROW row_user = mysql_fetch_row(select_result)){
        vector<string> tmp_vec;
        for(int j_column=0; j_column < 4; j_column ++ ){
            string tmp_str = row_user[j_column];
            tmp_vec.push_back(tmp_str);
        }
        grouplist.push_back(tmp_vec);
    }
    // CloseSqlConn(sql_conn);
    return grouplist;
}


